<?php
/**
 * Created by PhpStorm.
 * User: admin
 * Date: 2018/4/27
 * Time: 20:52
 */
namespace Huangzikun\hawkeye_spider;
use PDO;
use PDOException;

class MysqlClass extends BaseClass
{
    public $host;
    public $port;
    public $user;
    public $password;
    public $apiAddress;
    public $spiderInfo;

    protected $globalStatus = [
        'questions',//总执行sql数
        'Com_select',//总查询数
        'Com_insert',//总插入数
        'Com_update',
        'Com_delete',
        'Threads_connected',//当前连接数
        'Threads_running',//当前运行的连接
        'Connection_errors_internal',//服务器错误导致的失败连接数
        'Aborted_connects',//尝试与服务器进行连接结果失败的次数
        'Connection_errors_max_connections',//由 max_connections 限制导致的失败连接数
        'innodb_page_size' //innoDB分页大小
    ];

    protected $globalVariables = [
        'max_connections', //最大连接数
        'innodb_buffer_pool_chunk_size',//缓冲池大小，默认128MB
    ];

    public function __construct($apiAddress)
    {
        parent::__construct($apiAddress);
    }

    public function grab()
    {
        $pdo = $this->connection();
        if($pdo == null){
            return ;
        }

        foreach ($this->globalStatus as $param){
            $result = $pdo->query($this->getGlobalStatusSql($param));
            $this->getVariableValue($result);
        }

        foreach ($this->globalVariables as $param){
            $result = $pdo->query($this->getVariablesSql($param));
            $this->getVariableValue($result);
        }

        $this->getAvgTime($pdo);
        $this->getErrorSql($pdo);
    }

    /**
     * 获取 global status sql
     * @param $param
     * @return string
     */
    protected function getGlobalStatusSql($param)
    {
        return "SHOW GLOBAL STATUS where Variable_name = \"{$param}\"";
    }

    /**
     * 获取variables sql
     * @param $param
     * @return string
     */
    protected function getVariablesSql($param)
    {
        return "SHOW GLOBAL VARIABLES where Variable_name = \"{$param}\"";
    }

    /**
     * @param $pdo
     */
    protected function getVariableValue($pdo)
    {
        foreach ($pdo as $row){
            $this->spiderInfo[$row['Variable_name']] = $row['Value'];
        }
    }

    /**
     * @param PDO $pdo
     */
    protected function getAvgTime($pdo)
    {
        $avgTimeSql = 'SELECT schema_name, SUM(count_star) count, ROUND((SUM(sum_timer_wait) / SUM(count_star)) / 1000000) AS avg_microsec 
                  FROM performance_schema.events_statements_summary_by_digest 
                  WHERE schema_name IS NOT NULL AND 
                  schema_name NOT IN ("performance_schema","phpmyadmin","mysql")
                  GROUP BY schema_name';

        $avgTime = $pdo->query($avgTimeSql);
        $this->spiderInfo['database_info'] = array();

        foreach ($avgTime as $row){
            $this->spiderInfo['database_info'][$row['schema_name']] = [
                'count' => $row['count'],
                'avg_microsec' => $row['avg_microsec']
            ];
        }
    }

    /**
     * @param PDO $pdo
     */
    protected function getErrorSql($pdo)
    {
        $errorSql = 'SELECT schema_name, SUM(sum_errors) err_count FROM performance_schema.events_statements_summary_by_digest WHERE 
                  schema_name IS NOT NULL AND
                  schema_name NOT IN ("performance_schema","phpmyadmin","mysql")
                  GROUP BY schema_name';

        $error = $pdo->query($errorSql);
        foreach ($error as $row){
            $this->spiderInfo['database_info'][$row['schema_name']]['error_count'] = $row['err_count'];
        }
    }

    /**
     * 连接
     * @return null|PDO
     */
    protected function connection()
    {
        $dsn = "mysql:host={$this->host};port={$this->port};charset=utf8";
        $options = [
            PDO::ATTR_ERRMODE => 0,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ];
        $pdo = null;
        try{
            $pdo = new PDO($dsn, $this->user, $this->password, $options);
        }catch (PDOException $exception){
            echo "mysql connect error: {$exception->getMessage()}";
        }
        return $pdo;
    }
}